﻿using Coldairarrow.Entity.PB;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_CustomerBusiness : BaseBusiness<PB_Customer>, IPB_CustomerBusiness, ITransientDependency
    {
        public PB_CustomerBusiness(IDbAccessor db)
            : base(db)
        {
        }

        #region 外部接口

        public async Task<PageResult<PB_Customer>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_Customer>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_Customer, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_Customer> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        [DataAddLog(UserLogType.客户管理, "Name", "客户")]
        [DataRepeatValidate(new string[] { "Name", "Code" }, new string[] { "客户", "编码" })]
        public async Task AddDataAsync(PB_Customer data)
        {
            await InsertAsync(data);
        }

        [DataEditLog(UserLogType.客户管理, "Name", "客户")]
    
        public async Task UpdateDataAsync(PB_Customer data)
        {
            await UpdateAsync(data);
        }

        [DataDeleteLog(UserLogType.客户管理, "Name", "客户")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            List<PB_PickUpMain> PickUpMainList = await Db.GetIQueryable<PB_PickUpMain>().Where(x => ids.Contains(x.CusID)).ToListAsync();
            if (PickUpMainList.Count > 0)
            {
                throw new BusException("已被引用不能删除");
            }
            await DeleteAsync(ids);
        }

        public async Task AddDataExlAsync(List<PB_Customer> list)
        {
            await InsertAsync(list);   

        }

        /// <summary>
        /// 启用客户状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async  Task UpdateState(string id)
        {
            await Db.ExecuteSqlAsync("update PB_Customer  set states=1 where id ='"+id+"'");
        }

        public async Task UpdataStatefalse(string id)
        {
            //if (await Db.GetIQueryable<PB_DeliveryNotice>().AnyAsync(x => id == x.SignUnitId));
            //{
            //    throw new BusException("停用失败！该客户有未完工的任务!");
            //}
            //if (await Db.GetIQueryable<PB_DeliveryNotice>().AnyAsync(x => ids.Contains(x.ParentId)))
            //{
            //    throw new BusException("删除失败！该工艺下有工序禁止删除！");
            //}
            await Db.ExecuteSqlAsync("update PB_Customer  set states=0 where id ='" + id + "'");
        }

        /// <summary>
        /// 客户发货统计
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<CustomerDeliverDTO> GetCustomerDeliverListAsync(CustomerDeliverConditionDTO input)
        {
            int index = (input.pageIndex - 1) * input.pageRows + 1;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" select * from ( ");
            sqlStr.Append(" select ROW_NUMBER()over(order by MaterialId desc) as Rnumber,MaterialId,`Code`,`Name`,Spec,Weight,SUM(OutNum) as OutNum,SUM(OutNum)*Weight as Weights from ( ");
            sqlStr.Append(" select b.MaterialId,m.`Code`,m.`Name`,m.Spec,DATE_FORMAT(a.OutTime,'%Y-%m-%d') as OutTime,m.Weight,b.OutNum from TD_OutStorage as a ");
            sqlStr.Append(" join TD_OutStorRecord as b on a.Id=b.OutStorId  ");
            sqlStr.Append(" join PB_Material as m on b.MaterialId=m.Id ");
            sqlStr.Append(" join PB_PickUpMain as c on a.RefCode=c.Id ");
            sqlStr.Append(" join PB_DeliveryNoticeItem as d on c.ParentId=d.Id ");
            sqlStr.Append(" join PB_DeliveryNotice as e on d.ParentId=e.Id ");
            sqlStr.Append(" where a.OutType='Stockup' and IFNULL(e.SaleUnitId,'')!='' and b.OutNum>0 ");
            //物料信息搜索
            if (!input.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append(" and (m.`Name` like '%"+ input.Keyword + "%' or m.`Code` like '%" + input.Keyword + "%') ");
            }
            //购销单位搜索
            if (!input.SaleUnitName.IsNullOrEmpty())
            {
                sqlStr.Append(" and e.SaleUnitId= '" + input.SaleUnitName + "' ");
            }
            //日期段搜索
            if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
            {
                sqlStr.Append(" and DATE_FORMAT(a.OutTime,'%Y-%m-%d') BETWEEN '" + input.StartDate + "' and '" + input.EndDate + "' ");
            }
            sqlStr.Append(" ) as t group by MaterialId,`Code`,`Name`,Spec,Weight ");
            sqlStr.Append(" )as tt  ");
            sqlStr.Append(" where Rnumber BETWEEN " + index + " and (" + input.pageIndex * input.pageRows + ") ");
            List<CustomerDeliverDetailDTO> listdata = await Db.GetListBySqlAsync<CustomerDeliverDetailDTO>(sqlStr.ToString());
            if (listdata.Count > 0)
            {
                CustomerDeliverDTO CDeliver = new CustomerDeliverDTO();

                StringBuilder sqlCount = new StringBuilder();
                sqlCount.Append(" select Count(*) as TotalCount from ( ");
                sqlCount.Append(" select ROW_NUMBER()over(order by MaterialId desc) as Rnumber,MaterialId,`Code`,`Name`,Spec,Weight,SUM(OutNum) as OutNum,SUM(OutNum)*Weight as Weights from ( ");
                sqlCount.Append(" select b.MaterialId,m.`Code`,m.`Name`,m.Spec,DATE_FORMAT(a.OutTime,'%Y-%m-%d') as OutTime,m.Weight,b.OutNum from TD_OutStorage as a ");
                sqlCount.Append(" join TD_OutStorRecord as b on a.Id=b.OutStorId  ");
                sqlCount.Append(" join PB_Material as m on b.MaterialId=m.Id ");
                sqlCount.Append(" join PB_PickUpMain as c on a.RefCode=c.Id ");
                sqlCount.Append(" join PB_DeliveryNoticeItem as d on c.ParentId=d.Id ");
                sqlCount.Append(" join PB_DeliveryNotice as e on d.ParentId=e.Id ");
                sqlCount.Append(" where a.OutType='Stockup' and IFNULL(e.SaleUnitId,'')!='' and b.OutNum>0 ");
                //物料信息搜索
                if (!input.Keyword.IsNullOrEmpty())
                {
                    sqlCount.Append(" and (m.`Name` like '%" + input.Keyword + "%' or m.`Code` like '%" + input.Keyword + "%') ");
                }
                //购销单位搜索
                if (!input.SaleUnitName.IsNullOrEmpty())
                {
                    sqlStr.Append(" and e.SaleUnitId= '" + input.SaleUnitName + "' ");
                }
                //日期段搜索
                if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
                {
                    sqlCount.Append(" and DATE_FORMAT(a.OutTime,'%Y-%m-%d') BETWEEN '" + input.StartDate + "' and '" + input.EndDate + "' ");
                }
                //
                sqlCount.Append(" ) as t group by MaterialId,`Code`,`Name`,Spec,Weight ");
                sqlCount.Append(" )as tt  ");
                var tbdata = await Db.GetDataTableWithSqlAsync(sqlCount.ToString());
                CDeliver.Count = Convert.ToInt32(tbdata.Rows[0]["TotalCount"]);
                CDeliver.CustomerDeliverDetail = listdata;
                return CDeliver;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }

        /// <summary>
        /// 客户发货统计导出
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<CustomerDeliverDetailDTO>> GetCustomerDeliverExcelAsync(CustomerDeliverConditionDTO input)
        {
            int index = (input.pageIndex - 1) * input.pageRows + 1;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" select * from ( ");
            sqlStr.Append(" select ROW_NUMBER()over(order by MaterialId desc) as Rnumber,MaterialId,`Code`,`Name`,Spec,Weight,SUM(OutNum) as OutNum,SUM(OutNum)*Weight as Weights from ( ");
            sqlStr.Append(" select b.MaterialId,m.`Code`,m.`Name`,m.Spec,DATE_FORMAT(a.OutTime,'%Y-%m-%d') as OutTime,m.Weight,b.OutNum from TD_OutStorage as a ");
            sqlStr.Append(" join TD_OutStorRecord as b on a.Id=b.OutStorId  ");
            sqlStr.Append(" join PB_Material as m on b.MaterialId=m.Id ");
            sqlStr.Append(" join PB_PickUpMain as c on a.RefCode=c.Id ");
            sqlStr.Append(" join PB_DeliveryNoticeItem as d on c.ParentId=d.Id ");
            sqlStr.Append(" join PB_DeliveryNotice as e on d.ParentId=e.Id ");
            sqlStr.Append(" where a.OutType='Stockup' and IFNULL(e.SaleUnitId,'')!='' and b.OutNum>0 ");
            //物料信息搜索
            if (!input.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append(" and (m.`Name` like '%" + input.Keyword + "%' or m.`Code` like '%" + input.Keyword + "%') ");
            }
            //购销单位搜索
            sqlStr.Append(" and e.SaleUnitId= '" + input.SaleUnitName + "' ");
            //日期段搜索
            if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
            {
                sqlStr.Append(" and DATE_FORMAT(a.OutTime,'%Y-%m-%d') BETWEEN '" + input.StartDate + "' and '" + input.EndDate + "' ");
            }
            sqlStr.Append(" ) as t group by MaterialId,`Code`,`Name`,Spec,Weight ");
            sqlStr.Append(" )as tt  ");
            sqlStr.Append(" where Rnumber BETWEEN " + index + " and (" + input.pageIndex * input.pageRows + ") ");
            List<CustomerDeliverDetailDTO> listdata = await Db.GetListBySqlAsync<CustomerDeliverDetailDTO>(sqlStr.ToString());
            return listdata;
        }

        #endregion

        #region 私有成员

        #endregion
    }
}